package com.springboot.demo.mapper;

import com.springboot.demo.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.StatementType;
import org.aspectj.weaver.ast.Var;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.DeleteMapping;

import javax.persistence.ExcludeSuperclassListeners;
import javax.validation.constraints.Size;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author wubc683
 * @desc
 * @date 2021-09-13 9:51
 */
public interface SceneMapper {
    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where id = #{sid} and ownerId = #{uid}")
    Scene getSceneBySidAndOwnerId(Long sid, Long uid);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude " + "from web_scene " +
            "where id = #{sid}")
    Scene getSceneById(Long sid);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address longitude," +
            "latitude from web_scene " +
            "where ownerId = #{uid} and name = #{name}")
    Scene getSceneByUidAndSceneName(Long uid, String name);

    @Insert("insert into web_scene(sceneGroupId,name,state,ownerId,createDate,description,size,address,longitude," +
            "latitude) values" +
            "(#{sceneGroupId},#{name},#{state},#{ownerId},#{createDate},#{description},#{size},#{address}," +
            "#{longitude},#{latitude})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveSceneInfo(Scene scene);

    @Select("select name from web_scene where ownerId = #{uid}")
    List<String> getSceneNameByUid(Long uid);

    @Delete("delete from web_scene where id = #{sid}")
    void deleteSceneById(Long sid);

    @Select("select id,connectNo,sceneId,name,driveType,port from web_connection where sceneId = #{sid}")
    List<Connection> getSceneConnectBySid(Long sid);

    @Select("select id,varNo ,name,connectNo ,sceneId,dataType ,len,varAddr ,cycleTime,area,subArea,addOffset," +
            "bitOffset " + "from web_variable where sceneId = #{sid}")
    List<Variable> getSceneVarBySid(Long sid);

    @Select("select id,alarmNo,sceneId,alarmText,trigVarNo,limitFlag,limitVarNo,limitValue,trigMode from " +
            "web_analog_alarm where" + " sceneId = #{sid}")
    List<AnalogAlarm> getSceneAnalogAlarmBySid(Long sid);

    @Select("select id,alarmNo,sceneId,alarmText,trigVarNo,trigBit from web_discrete_alarm where sceneId = #{sid}")
    List<DiscreteAlarm> getSceneDiscreteAlarmBySid(Long sid);

    @Insert("<script>" + " insert into web_connection(connectNo,sceneId,name,driveType,port)" +
            " values " + "<foreach item='item' collection='connList' separator=','> " +
            "(#{item.connectNo},#{sid},#{item.name},#{item.driveType},#{item.port})" + " </foreach> " +
            "</script>")
    void saveSceneConnData(Long sid, List<Connection> connList);

    @Insert("<script>" + "insert into web_variable(varNo,sceneId,name,connectNo,dataType,len,cycleTime,varAddr)" +
            " values " + "<foreach item='item' collection='varList' separator=','>" +
            "(#{item.varNo},#{sid},#{item.name},#{item.connectNo},#{item.dataType},#{item.len},#{item.cycleTime}," +
            "#{item.varAddr})" +
            "</foreach>" + "</script>")
    void saveSceneVarData(Long sid, List<Variable> varList);

    @Insert("<script>" + "insert into web_analog_alarm(alarmNo,sceneId,alarmText,trigVarNo,limitFlag,limitValue," +
            "limitVarNo,trigMode)" + " values " + "<foreach item='item' collection='analogAlarmList' " +
            "separator=','>" + "(#{item.alarmNo},#{sid},#{item.alarmText},#{item.trigVarNo},#{item.limitFlag}," +
            "#{item.limitValue},#{item.limitVarNo},#{item.trigMode})" + "</foreach>" + "</script>")
    void saveSceneAnalogAlarmData(Long sid, List<AnalogAlarm> analogAlarmList);

    @Insert("<script>" + "insert into web_discrete_alarm(alarmNo,sceneId,alarmText,trigVarNo,trigBit)" +
            " values <foreach item='item' collection='discreteAlarmList' separator=','>" +
            " (#{item.alarmNo},#{sid},#{item.alarmText},#{item.trigVarNo},#{item.trigBit}) </foreach>" +
            "</script>")
    void saveSceneDiscreteAlarmData(Long sid, List<DiscreteAlarm> discreteAlarmList);

    @Delete("delete from web_connection where sceneId = #{sid}")
    void delSceneConnData(Long sid);

    @Delete("delete from web_variable where sceneId = #{sid}")
    void delSceneVarData(Long sid);

    @Delete("delete from web_analog_alarm where sceneId = #{sid}")
    void delSceneAnalogAlarmData(Long sid);

//    @Delete("delete from web_discrete_alarm where sceneId = #{sid}")
//    void delSceneDiscreteAlarmData(Long sid);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where ownerId = #{uid} and sceneGroupId = 0")
    List<Scene> getUngroupedSceneInfo(Long uid);

    @Results({
            @Result(column = "id", property = "id"), //@Result：封装映射关系的子注解
            //column 属性：查询出的表中字段名称
            //property 属性：实体对象中的属性名称
            @Result(property = "sceneGroups",
                    javaType = List.class,
                    //javaType 属性：被包含对象的数据类型,把该列做参数传给查询语句
                    column = "{uid = uid,gid = id,sort = sort,search = search}",
                    many = @Many(select = "com.springboot.demo.mapper.SceneMapper.getSceneGroupByGroupParentId")
            ),//many 属性：一对多查询固定属性
            @Result(property = "scenes",
                    javaType = List.class,
                    //javaType 属性：被包含对象的数据类型
                    column = "{uid = uid,gid=id,sort = sort,search = search}",
                    many = @Many(select = "com.springboot.demo.mapper.SceneMapper.getSceneInfo")
            )//many 属性：一对多查询固定属性
    })
    @Select("select id,parentId,name,ownerId, rank,#{uid} as uid,#{sort,jdbcType=VARCHAR} as sort," +
            "#{search,jdbcType=VARCHAR} as search from " +
            "web_scene_group " + "where id = " + "#{gid}")
    SceneGroup getSceneGroupByGroupId(Long uid, Long gid, String sort, String search);

    @Results({
            @Result(column = "id", property = "id"), //@Result：封装映射关系的子注解
            //column 属性：查询出的表中字段名称
            //property 属性：实体对象中的属性名称
            @Result(property = "sceneGroups",
                    javaType = List.class,
                    //javaType 属性：被包含对象的数据类型,把该列做参数传给查询语句
//                    column = "id",
                    column = "{uid = uid,gid = id,sort = sort,search = search}",
                    many = @Many(select = "com.springboot.demo.mapper.SceneMapper.getSceneGroupByGroupParentId")
            ),//many 属性：一对多查询固定属性
            @Result(property = "scenes",
                    javaType = List.class,
                    //javaType 属性：被包含对象的数据类型
                    column = "{uid = uid,gid = id,sort = sort,search = search}",
                    many = @Many(select = "com.springboot.demo.mapper.SceneMapper.getSceneInfo")
            )//many 属性：一对多查询固定属性
    })
    @Select("select id,parentId,name,ownerId, rank,#{uid} as uid,#{sort,jdbcType=VARCHAR} as sort," +
            "#{search,jdbcType=VARCHAR} as search" +
            " from web_scene_group where parentId = #{gid}")
    List<SceneGroup> getSceneGroupByGroupParentId(Long uid, Long gid, String sort, String search);

    @Select("select id,parentId,name,ownerId ,rank from web_scene_group where id = #{gid}")
    SceneGroup getSceneGroupByGid(Long gid);

    @Select("select name from web_scene_group where ownerId = #{uid}")
    List<String> getAllSceneGroupNameByUid(Long uid);

    @Insert("insert into web_scene_group(parentId,name,ownerId,rank) values(#{pid},#{name},#{uid},#{rank})")
    void saveSceneGroup(Long uid, Long pid, String name, int rank);

    @Update("update web_scene_group set parentId = #{pid},name = #{name} where id = #{gid}")
    void updateSceneGroup(Long pid, Long gid, String name);

    @Delete("delete from web_scene_group where id = #{gid}")
    void delSceneGroupById(Long gid);

    @Update({"<script>", "delete from web_scene_group where id in ", "<foreach item = 'item' " +
            "collection='sceneGroups' open='(' close=')' separator=','>", "#{item.id}", "</foreach>", "</script>"})
    void delSceneGroup(List<SceneGroup> sceneGroups);

    @SelectProvider(type = SqlMapper.class, method = "getSceneInfo")
    List<Scene> getSceneInfo(Long uid, Long gid, String sort, Date startDate, Date endDate, String search);

    @Update("update web_scene set sceneGroupId = #{sceneGroupId},name = #{name},state = #{state},ownerId = " +
            "#{ownerId},description = " + "#{description}," + "address " + "= #{address} ,createDate = " +
            "#{createDate} where id = #{id}")
    void updateSceneInfo(Scene scene);

    @Select("select c.sceneId from web_connection c,web_variable v,web_analog_alarm a,web_discrete_alarm d where c" +
            ".sceneId" + " = " + "#{sid} and v.sceneId = #{sid} and a.sceneId = #{sid} and d.sceneId = #{sid}")
    Long getSceneIdById(Long sid);

//    @Delete("delete c,v,a,d from web_connection c," +
//            "web_variable v,web_analog_alarm a,web_discrete_alarm d where c.sceneId = #{sceneId} and v" +
//            ".sceneId = " + "#{sceneId} and a.sceneId = #{sceneId} and d.sceneId = #{sceneId}")
//    void delSceneDataById(Long sceneId);

    @Select("select id,varNo ,name,connectNo ,sceneId,dataType ,len,varAddr ,cycleTime,area,subArea,addOffset," +
            "bitOffset " + " from web_variable where sceneId = #{sid} and dataType != #{b} and dataType != #{s}")
    List<Variable> getAnalogTrigVarBySid(Long sid, Integer b, Integer s);

    @Select("select id,varNo ,name,connectNo ,sceneId,dataType ,len,varAddr ,cycleTime,area,subArea,addOffset," +
            "bitOffset " +
            " from web_variable where sceneId = #{sid} and dataType != #{byte0} and dataType != #{char0} and dataType" +
            " " + "!= #{dInt} and dataType " + "!= #{dWord} " +
            "and dataType != #{real} and dataType != #{timer}")
    List<Variable> getDiscreteTrigVarBySid(Long sid, Integer byte0,Integer char0,Integer dInt, Integer dWord,
                                           Integer real, Integer timer);

    @Select("select sceneId from web_connection where id = #{connectId} and sceneId in (select id " +
            "from web_scene where ownerId = #{uid})")
    Long getSceneIdByUidAndConnId(Long uid, Long connectId);

    @Select("select id,connectNo,sceneId,name,driveType,port from web_connection where id = #{connectId}")
    Connection getSceneConnectById(Long connectId);

    @Select("select id,connectNo,sceneId,name,driveType,port from web_connection where sceneId = #{sid}")
    List<Connection> getSceneConnectInfoBySid(Long sid);

    @Insert("insert into web_connection(connectNo,sceneId,name,driveType,port) values(#{connectNo},#{sceneId}," +
            "#{name},#{driveType},#{port})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveSceneConnect(Connection connection);

    @Update("update web_connection set connectNo = #{connectNo},name = #{name},driveType = " +
            "#{driveType},port = #{port} where id = #{id}")
    void updateSceneConnect(Connection connection);

    @Delete("<script> delete from web_connection where id in <foreach item = 'item' collection = 'connIds' open='(' " +
            "close=')' " + "separator=','> " + "#{item} </foreach> </script>")
    void delSceneConnect(List<Long> connIds);

    @Select("select sceneId from web_variable where id = #{varId} and sceneId in (select id from web_scene where " +
            "ownerId = #{uid})")
    Long getSceneIdByUidAndVarId(Long uid, Long varId);

    @Select("select id,varNo ,name,connectNo ,sceneId,dataType ,len,varAddr ,cycleTime,area,subArea,addOffset," +
            "bitOffset " + " from web_variable where id = #{varId}")
    Var getSceneVarByVarId(Long varId);

    @Insert("insert into web_variable(varNo,name,connectNo,sceneId,dataType,len,cycleTime,varAddr) values(#{varNo}," +
            "#{name},#{connectNo},#{sceneId},#{dataType},#{len},#{cycleTime},#{varAddr})")
    void saveSceneVariable(Variable v);

    @Update("update web_variable set varNo = #{varNo},name = #{name},connectNo = #{connectNo}," +
            "dataType=#{dataType},len = #{len},cycleTime = #{cycleTime},varAddr = #{varAddr} where id = #{id}")
    void updateSceneVariable(Variable v);

    @Delete("<script> delete from web_variable where id in <foreach item = 'item' collection='varIds' open='(' " +
            "close=')' separator=','> #{item} </foreach> </script>")
    void delSceneVariable(List<Long> varIds);

    @Select("select id,alarmNo,sceneId,alarmText,trigVarNo,limitFlag,limitVarNo,limitValue,trigMode" +
            " from web_analog_alarm where id = #{analogId} and sceneId in (select sceneId from web_scene" +
            " where" + " ownerId = #{uid})")
    AnalogAlarm getSceneAnalogByUidAndAnalogId(Long uid, Long analogId);

    @Insert("insert into web_analog_alarm(alarmNo,sceneId,alarmText,trigVarNo,limitFlag,limitValue,limitVarNo," +
            "trigMode) " +
            "values(#{alarmNo},#{sceneId},#{alarmText},#{trigVarNo},#{limitFlag},#{limitValue},#{limitVarNo}," +
            "#{trigMode})")
    void saveSceneAnalog(AnalogAlarm analog);

    @Select("select sceneId from web_analog_alarm where id = #{analogId} and sceneId in (select sceneId from " +
            "web_scene where ownerId = #{uid})")
    Long getSceneIdByUidAndAnalogId(Long uid, Long analogId);

    @Update("update web_analog_alarm set alarmNo= #{alarmNo},alarmText=#{alarmText},trigVarNo = #{trigVarNo}," +
            "limitFlag = #{limitFlag},limitVarNo = #{limitVarNo},limitValue=#{limitValue},trigMode=#{trigMode} where " +
            "id = #{id}")
    void updateSceneAnalog(AnalogAlarm analog);

    @Delete("<script> delete from web_analog_alarm where id in <foreach item='item' collection='analogIds' " +
            "open='(' close=')' separator=','>#{item} </foreach> </script>")
    void deleteSceneAnalog(List<Long> analogIds);

//    @Select("select sceneId from web_discrete_alarm where id = #{discreteId} and sceneId in (select sceneId from " +
//            "web_scene where ownerId = #{uid})")
//    Long getSceneIdByUidAndDiscreteId(Long uid, Long discreteId);

    @Select("select id,alarmNo,sceneId,alarmText,trigVarNo,trigBit from web_discrete_alarm " +
            "where id = #{discreteId} and sceneid in (select sceneId from " +
            "web_scene where ownerId = #{uid})")
    DiscreteAlarm getSceneDiscreteByUidAndDiscreteId(Long uid, Long discreteId);

    @Select("select id,connectNo,sceneId,name,driveType,port from web_connection where id = #{connectId} and " +
            "sceneId in (select sceneId from web_scene " +
            "where ownerId = #{uid})")
    Connection getSceneConnectByUidAndConnectId(Long uid, Long connectId);

    @Select("select id,varNo ,name,connectNo ,sceneId,dataType ,len,varAddr ,cycleTime,area,subArea,addOffset," +
            "bitOffset " +
            " from web_variable where id = #{varId} and sceneId in (select sceneId from web_scene where " +
            "ownerId = #{uid})")
    Variable getSceneVariableByUidAndVarId(Long uid, Long varId);

    @Insert("insert into web_discrete_alarm(alarmNo,sceneId,alarmText,trigVarNo,trigBit) values(#{alarmNo}," +
            "#{sceneId},#{alarmText},#{trigVarNo},#{trigBit})")
    void saveSceneDiscrete(DiscreteAlarm discrete);

    @Update("update web_discrete_alarm set alarmNo = #{alarmNo} ,alarmText = #{alarmText} , " +
            "trigVarNo=#{trigVarNo},trigBit=#{trigBit} where id = #{id}")
    void updateSceneDiscrete(DiscreteAlarm discrete);

    @Delete("<script> delete from web_discrete_alarm where id in <foreach item='item' " +
            "collection='discreteIds' open='(' close=')' separator=','> #{item} </foreach> </script>")
    void deleteSceneDiscrete(List<Long> discreteIds);

    @Update("update web_scene set state = #{state} where id = #{sceneId}")
    void updateSceneState(Long sceneId, Integer state);

    @Select("select 1 from web_scene where id = #{sid} and ownerId = #{uid} limit 1")
    Integer select1ByUidAndSid(Long uid, Long sid);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where id = #{sid} and ownerId = #{uid}")
    Scene getSceneByUidAndSid(Long uid, Long sid);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where ownerId = #{uid} and id = (select sceneId from web_connection where id = " +
            "#{connId})")
    Scene getSceneByUidAndConnId(Long uid, Long connId);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where ownerId = #{uid} and id = (select sceneId from web_variable where id = " +
            "#{varId})")
    Scene getSceneByUidAndVarId(Long uid, Long varId);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where ownerId = #{uid} and id = (select sceneId from web_analog_alarm where id =" +
            " #{analogId})")
    Scene getSceneByUidAndAnalogId(Long uid, Long analogId);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where ownerId = #{uid} and id = (select sceneId from web_discrete_alarm where id" +
            " = #{discreteId})")
    Scene getSceneByUidAndDiscreteId(Long uid, Long discreteId);

    @Select("select id,parentId,name,ownerId ,rank from web_scene_group where ownerId = #{uid}")
    List<SceneGroup> getSceneGroupByUid(Long uid);

    @Delete("delete from web_scene where sceneGroupId = #{gid}")
    void delSceneByGroupId(Long gid);

    @Delete("<script> delete from web_scene where sceneGroupId in <foreach item = 'item' collection='sceneGroups' " +
            " open='(' close=')' separator=','> #{item} </foreach> </script>")
    void delSceneBySceneGroups(List<SceneGroup> allSceneGroupFromSceneGroup);

    @Select("<script> select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address " +
            ",longitude,latitude from web_scene where ownerId = #{uid} and id in <foreach item = 'item' collection = " +
            "'sceneIds' open = '(' close = ')' separator = ','> #{item} </foreach> </script>")
    List<Scene> getSceneByUidAndSids(Long uid, List<Long> sceneIds);

    @Delete("<script> delete s,c,v,a,d" +
            " from  web_scene  s left join web_connection  c on s.id = c.sceneId " +
            " left join web_variable  v on s.id = v.sceneId " +
            " left join web_analog_alarm  a on s.id = a.sceneId " +
            " left join web_discrete_alarm d on s.id = d.sceneId " +
            " where s.id in <foreach item = 'item' collection='sceneIds' " +
            " open='(' close=')' separator=','> #{item} </foreach> </script>")
    void deleteSceneInfoAndDataBySids(List<Long> sceneIds);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where id = (select sceneId from web_connection where id = #{connId})")
    Scene getSceneByConnId(Long connId);

    @Select("select id from web_connection where sceneId = #{sceneId}")
    List<Long> getSceneConnIdBySceneId(Long sceneId);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where id = (select sceneId from web_variable where id = #{varId})")
    Scene getSceneByVarId(Long varId);

    @Select("select id from web_variable where sceneId = #{sceneId}")
    List<Long> getSceneVarIdBySceneId(Long sceneId);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where id = (select sceneId from web_analog_alarm where id = #{analogId})")
    Scene getSceneByAnalogId(Long analogId);

    @Select("select id from web_analog_alarm where sceneId = #{sceneId}")
    List<Long> getSceneAnalogIdBySceneId(Long sceneId);

    @Select("select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ,longitude," +
            "latitude from web_scene " +
            "where id = (select sceneId from web_discrete_alarm where id = #{discreteId})")
    Scene getSceneByDiscreteId(Long discreteId);

    @Select("select id from web_discrete_alarm where sceneId = #{sceneId}")
    List<Long> getSceneDiscreteIdBySceneId(Long sceneId);

    @Select("select c.id as connId,c.name as connName,c.deviceId as deviceId,d.deviceName as deviceName,d.macAddr as " +
            "macAddr from web_connection c left join" + " " +
            "web_device d on c.deviceId = d.id where " + "c.sceneId = #{sceneId}")
    List<ConnectDevice> getSceneDeviceBySceneId(Long sceneId);

    @Select("select id from web_connection where sceneId = #{sceneId}")
    List<Long> getSceneConnectIdBySid(Long sceneId);

    @Update("update web_connection set deviceId = #{deviceId} where id = #{connId} ")
    void updateConnDevice(ConnDevice connDevice);

    @Update("<script> <foreach item='item' collection='connIds' separator=';'> update web_connection set " +
            "deviceId = null where connId = #{item} </foreach> </script>")
    void deleteSceneDevice(List<Long> connIds);

    @Update("update web_connection set deviceId = #{deviceId} where id = #{connId}")
    void saveSceneDeviceByConnIdAndDeviceId(Long connId, Long deviceId);

    @Select("select id,name,type,createDate,isDefault from web_scene_panel where sceneId = #{sid}")
    List<Panel> getSceneAllPanels(Long sid);

    @Insert("insert into web_panel(name,type,isDefault,createDate) values(#{name},#{type}" +
            ",#{isDefault}," + "#{createDate})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveScenePanelInfo(Panel scenePanel);

    @Select("select name from web_scene_panel where sceneId = #{sid}")
    List<String> getSceneAllPanelNames(Long sid);

    @Select("<script> select id,sceneGroupId,name,state, ownerId,createDate,description,size,readWrite,address ," +
            "longitude,latitude from " + "web_scene as ws " +
            "where ownerId = #{uid} and state >= #{state} <if test='search != " +
            "null and search != \"\"'> and concat(ws.id,ws.name) like concat('%', #{search},'%') </if> </script>")
    List<Scene> getScenesByUidAndSearch(Long uid, String search, Integer state);

    @Select("select id from web_scene where ownerId = #{uid}")
    List<Long> getSceneIdsByUid(Long uid);

    @Select("select d.longitude,d.latitude from web_device d " +
            " left join web_connection c on c.deviceId = d.id  where c.sceneId = #{sceneId} and d.deleteFlag = 0")
    List<Device> getDeviceAddrBySceneId(Long sceneId);

    @Insert("insert into web_scene_panel(sceneId,panelId) values(#{sceneId},#{panelId})")
    void saveScenePanelBySceneIdAndPanelId(Long sceneId, Long panelId);

    @Update("<script> <foreach item='item' collection='sceneVarBySid' separator=';'> update web_variable set " +
            "subArea = #{item.subArea},area = #{item.area},addOffset = #{item.addOffset},bitOffset = #{item" +
            ".bitOffset} " + "where sceneId = #{sceneId} and id = #{item.id} </foreach> </script>")
    void updateSceneVarAddr(Long sceneId, List<Variable> sceneVarBySid);

    @Select("select 1 from web_runningscene where sceneId = #{sceneId}")
    Integer isSceneIdExistedInWebRunningScene(Long sceneId);

    @Select("select deviceId from web_connection where sceneId = #{sceneId}")
    List<Long> getSceneConnectIdBySceneId(Long sceneId);

    @Update("<script> update web_connection set deviceId = -1 where deviceId in <foreach item = 'item' " +
            " collection='deviceIds'" +
            " separator=',' open = '(' close = ')' > #{item} </foreach> </script>")
    void updateSceneConnectDeviceId(List<Long> deviceIds);

    @Delete("<script> delete from web_connection where id in <foreach item = 'item' collection='connIds' open='(' " +
            "close=')' separator=','> #{item} </foreach> </script>")
    void deleteSceneConnections(List<Long> connIds);

//    @Delete("delete from web_runningscene where sceneid = #{sceneId}")
//    void deleteRunningScene(Long sceneId);
//
//    @Insert("insert into web_runningscene(sceneid,startdate,startuser) values(#{sceneId},#{date},#{uid})")
//    void saveRunningScene(Long sceneId, Date date, Long uid);

    @Select("select id,micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate,online,address,province,city" +
            ",region,longitude,latitude from web_device where deleteFlag = 0 and id in (select deviceId from " +
            "web_connection where " + "sceneId = #{sceneId})")
    List<Device> getSceneDevices(Long sid);

    @Select("select name from web_scene where id = #{sceneId}")
    String getSceneNameById(Long sceneId);

    @Select("select id,varNo ,name,connectNo ,sceneId,dataType ,len,varAddr ,cycleTime,area,subArea,addOffset," +
            "bitOffset from web_variable where sceneId = #{sid} and varNo = #{varNo}")
    Variable getSceneVarBySidAndVarNo(Long sid, Integer varNo);

    @Update("<script> update web_scene_group set rank = rank+#{dist} where id in <foreach item = 'item' " +
            " collection='ids'" + " separator=',' open = '(' close = ')' > #{item} </foreach> </script>")
    void updateSceneGroupRank(List<Long> ids, int dist);

    @Select("select name from web_scene where name like concat(#{sceneNamePrefix},'%')")
    List<String> getSceneNameByPrefix(String sceneNamePrefix);

    @Select("select d.id as deviceId, d.deviceName as deviceName,d.macAddr as macAddr,c.id as connId  ,c.name as " +
            "connName from " +
            "web_device" + " d inner join " +
            "web_user_device ud on d.id = ud.deviceId  left join web_connection c on c.deviceId = d.id " +
            "and c.sceneId = #{sceneId}" +
            " where d.deleteFlag = 0 and d.online = #{devStatus} and ud.userId= #{userId} ")
    List<ConnectDevice> getSceneConnectDevices(Long userId,Long sceneId,Integer devStatus);

    @Select("select count(1) from web_user_device where userId = #{uid} and deviceId = #{deviceId}")
    Integer isDeviceInMyDevice(Long uid, Long deviceId);

    @Select("select count(1) from web_connection where sceneId = #{sceneId} and " +
            "deviceId = " + "#{connDevice.deviceId} and id != #{connDevice.connId}  ")
    Integer isDeviceConfigSceneConn(Long sceneId, ConnDevice connDevice);

    @Select("select d.longitude as longitude,d.latitude as latitude from web_device d inner join web_connection c " +
            "on d.id = c.deviceId where c.sceneId = #{sceneId}")
    List<Device> getSceneDeviceAddresses(Long sceneId);

    @Update("update web_scene set longitude = #{longitude} ,latitude=#{latitude} where id = " +
            "#{sceneId}")
    void updateSceneStateAndAddress(Long sceneId,Double longitude, Double latitude);

    @Select("select deviceId from web_connection where id = #{connId}")
    Long getSceneConnectDeviceIdByConnId(Long connId);

    @Select("select s.id as id,s.ownerId as ownerId ,s.state as state from web_scene s inner join web_connection c on" +
            " s.id = c.sceneId " + "where c.deviceId = #{deviceId}")
    List<Scene> getSceneByDeviceId(Long deviceId);

}